package com.guigu.qingcheng.orderservice.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.guigu.qingcheng.pojo.order.TbCategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author testjava
 * @since 2020-07-08
 */
public interface TbCategoryReportMapper extends BaseMapper<TbCategoryReport> {

    @Select("\n" +
            "SELECT category_id1 ,category_id2,category_id3\n" +
            ",DATE_FORMAT(o.`pay_time`,'%Y‐%m‐%d' ) count_date,SUM(oi.num)\n" +
            "num,SUM(oi.pay_money) money\n" +
            "FROM tb_order_item oi, tb_order o\n" +
            "WHERE oi.`order_id`=o.`id` AND o.`pay_status`='1' AND\n" +
            "DATE_FORMAT(o.`pay_time`,'%Y‐%m‐%d' ) =#{date}\n" +
            "GROUP BY`category_id1`,`category_id2`,`category_id3`,DATE_FORMAT(o.`pay_time`,'%Y‐%m‐%d' )")
    public List<TbCategoryReport> categoryReports (@Param("date")LocalDate date);

    /**
     * 按时间段统计一级类目
     * @param date1
     * @param date2
     * @return
     */
    @Select("SELECT cr.category_id1 categoryId1, \n" +
            " c.name categoryName, SUM(num) num, \n" +
            " SUM(money) money FROM tb_category_report cr, v_category1 c \n" +
            " WHERE cr.category_id1 = c.id AND count_date >= #{date1} AND count_date <= #{date2}\n" +
            " GROUP BY cr.category_id1, c.name\n")
    public List<Map> category1Count(@Param("date1") String date1 , @Param("date2") String date2 );



}
